﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using Pixysoft.Framework.XSchEmA.Entity;

using Pixysoft.Framework.XSchEmA;
using Pixysoft.Framework.XSchEmA.Sql;



namespace Pixysoft.Framework.XSchEmA.Aspect
{
    class SqlServerSchemaAspect : ISchemaAspect
    {
        private const int default_varcharlength = 100;
        private const int default_mid_varcharlength = 4096;//2009-12-13 4096导致建表错误

        List<string> dataTypeList;
        List<string> systemNameList;
        public SqlServerSchemaAspect()
        {
            dataTypeList = Pixysoft.EnumCoder.ToList<SqlServerDataTypeCollection>();
            systemNameList = Pixysoft.EnumCoder.ToList<AccessKeyWordCollection>();
        }


        public DataTypeSchema GetDefaultDataType()
        {
            return new DataTypeSchema(
         SqlServerDataTypeCollection.TEXT.ToString(), -1, -1, -1);
        }

        public DataTypeSchema AutoCompleteDataType(string datatype)
        {
            if (datatype == null)
                throw new Exception("011001");

            if (!dataTypeList.Contains(datatype))
                return GetDefaultDataType();

            SqlServerDataTypeCollection datatypeEnum = Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(datatype);

            switch (datatypeEnum)
            {
                case SqlServerDataTypeCollection.TEXT:
                case SqlServerDataTypeCollection.NTEXT:
                case SqlServerDataTypeCollection.SQL_VARIANT:
                case SqlServerDataTypeCollection.IMAGE:
                case SqlServerDataTypeCollection.BIGINT:
                case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                case SqlServerDataTypeCollection.DATETIME:
                case SqlServerDataTypeCollection.SMALLDATETIME:
                case SqlServerDataTypeCollection.TIMESTAMP:
                case SqlServerDataTypeCollection.REAL:
                case SqlServerDataTypeCollection.FLOAT:
                case SqlServerDataTypeCollection.MONEY:
                case SqlServerDataTypeCollection.SMALLMONEY:
                case SqlServerDataTypeCollection.INT:
                case SqlServerDataTypeCollection.SMALLINT:
                case SqlServerDataTypeCollection.TINYINT:
                case SqlServerDataTypeCollection.BIT:
                default:
                    {
                        return new DataTypeSchema(datatypeEnum.ToString(), -1, -1, -1);
                    }


                case SqlServerDataTypeCollection.DECIMAL:
                case SqlServerDataTypeCollection.NUMERIC:
                    {
                        return new DataTypeSchema(datatypeEnum.ToString(), -1, 10, 0);
                    }


                case SqlServerDataTypeCollection.CHAR:
                case SqlServerDataTypeCollection.VARCHAR:
                case SqlServerDataTypeCollection.NCHAR:
                case SqlServerDataTypeCollection.NVARCHAR:
                case SqlServerDataTypeCollection.BINARY:
                case SqlServerDataTypeCollection.VARBINARY:
                    {
                        return new DataTypeSchema(datatypeEnum.ToString(), 255, -1, -1);
                    }
            }

        }

        public bool IsValidDataType(DataTypeSchema datatype)
        {
            datatype.Inspection();

            if (!dataTypeList.Contains(datatype.DataType.ToUpper()))
                return false;

            SqlServerDataTypeCollection datatypeEnum = Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(datatype.DataType);

            switch (datatypeEnum)
            {
                case SqlServerDataTypeCollection.TEXT:
                case SqlServerDataTypeCollection.NTEXT:
                case SqlServerDataTypeCollection.SQL_VARIANT:
                case SqlServerDataTypeCollection.IMAGE:
                case SqlServerDataTypeCollection.BIGINT:
                case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                case SqlServerDataTypeCollection.DATETIME:
                case SqlServerDataTypeCollection.SMALLDATETIME:
                case SqlServerDataTypeCollection.TIMESTAMP:
                case SqlServerDataTypeCollection.REAL:
                case SqlServerDataTypeCollection.FLOAT:
                case SqlServerDataTypeCollection.MONEY:
                case SqlServerDataTypeCollection.SMALLMONEY:
                case SqlServerDataTypeCollection.INT:
                case SqlServerDataTypeCollection.SMALLINT:
                case SqlServerDataTypeCollection.TINYINT:
                case SqlServerDataTypeCollection.BIT:
                default:
                    {
                        return true;
                    }


                case SqlServerDataTypeCollection.DECIMAL:
                case SqlServerDataTypeCollection.NUMERIC:
                    {
                        if (datatype.Precision < 1 || datatype.Precision > 38)
                            return false;
                        if (datatype.Scale < 0 || datatype.Scale > datatype.Precision || datatype.Scale > 12)
                            return false;

                        return true;
                    }


                case SqlServerDataTypeCollection.CHAR:
                case SqlServerDataTypeCollection.VARCHAR:
                case SqlServerDataTypeCollection.BINARY:
                    {
                        if (datatype.Length < 0 || datatype.Length > 8000)
                            return false;

                        return true;
                    }

                case SqlServerDataTypeCollection.NCHAR:
                case SqlServerDataTypeCollection.NVARCHAR:
                case SqlServerDataTypeCollection.VARBINARY:
                    {
                        if (datatype.Length < 0 || datatype.Length > 4000)
                            return false;

                        return true;
                    }
            }

        }

        public bool IsUniqueDataType(DataTypeSchema datatype)
        {
            return false;
        }

        public string GetDotNetDataType(DataTypeSchema datatypeSchema)
        {
            datatypeSchema.Inspection();

            SqlServerDataTypeCollection type = Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(datatypeSchema.DataType);

            switch (type)
            {
                case SqlServerDataTypeCollection.VARBINARY:
                case SqlServerDataTypeCollection.BINARY:
                case SqlServerDataTypeCollection.SQL_VARIANT:
                case SqlServerDataTypeCollection.IMAGE:
                    return "System.Byte[]";



                case SqlServerDataTypeCollection.SMALLINT:
                case SqlServerDataTypeCollection.TINYINT:
                case SqlServerDataTypeCollection.BIT:
                case SqlServerDataTypeCollection.INT:
                case SqlServerDataTypeCollection.BIGINT:
                    return "System.Int64";




                case SqlServerDataTypeCollection.DATETIME:
                case SqlServerDataTypeCollection.SMALLDATETIME:
                case SqlServerDataTypeCollection.TIMESTAMP:
                    return "System.DateTime";



                case SqlServerDataTypeCollection.DECIMAL:
                case SqlServerDataTypeCollection.NUMERIC:
                case SqlServerDataTypeCollection.REAL:
                case SqlServerDataTypeCollection.FLOAT:
                case SqlServerDataTypeCollection.MONEY:
                case SqlServerDataTypeCollection.SMALLMONEY:
                    return "System.Double";


                case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                case SqlServerDataTypeCollection.NCHAR:
                case SqlServerDataTypeCollection.NVARCHAR:
                case SqlServerDataTypeCollection.CHAR:
                case SqlServerDataTypeCollection.VARCHAR:
                case SqlServerDataTypeCollection.TEXT:
                case SqlServerDataTypeCollection.NTEXT:
                default:
                    return "System.String";
            }

        }

        public void TransformToGeneral(DatabaseSchema schema)
        {
            foreach (TableSchema table in schema.Tables)
            {
                foreach (ColumnSchema column in table.Columns)
                {

                    SqlServerDataTypeCollection type = Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(column.DataType.DataType);

                    switch (type)
                    {
                        case SqlServerDataTypeCollection.VARBINARY:
                        case SqlServerDataTypeCollection.BINARY:
                        case SqlServerDataTypeCollection.SQL_VARIANT:
                        case SqlServerDataTypeCollection.IMAGE:
                            {
                                column.DataType = new DataTypeSchema(DotNetDataType.Byte.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);
                                break;
                            }



                        case SqlServerDataTypeCollection.SMALLINT:
                        case SqlServerDataTypeCollection.TINYINT:
                        case SqlServerDataTypeCollection.BIT:
                        case SqlServerDataTypeCollection.INT:
                        case SqlServerDataTypeCollection.BIGINT:
                            {
                                column.DataType = new DataTypeSchema(DotNetDataType.Int64.ToString(), -1,
                                    column.DataType.Precision, column.DataType.Scale, column.DataType.IsAutoIncrement);
                                break;
                            }




                        case SqlServerDataTypeCollection.DATETIME:
                        case SqlServerDataTypeCollection.SMALLDATETIME:
                        case SqlServerDataTypeCollection.TIMESTAMP:
                            {
                                column.DataType = new DataTypeSchema(DotNetDataType.DateTime.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);
                                break;
                            }



                        case SqlServerDataTypeCollection.DECIMAL:
                        case SqlServerDataTypeCollection.NUMERIC:
                        case SqlServerDataTypeCollection.REAL:
                        case SqlServerDataTypeCollection.FLOAT:
                        case SqlServerDataTypeCollection.MONEY:
                        case SqlServerDataTypeCollection.SMALLMONEY:
                            {
                                column.DataType = new DataTypeSchema(DotNetDataType.Double.ToString(), -1,
                                    column.DataType.Precision, column.DataType.Scale, column.DataType.IsAutoIncrement);
                                break;
                            }


                        case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                        case SqlServerDataTypeCollection.NCHAR:
                        case SqlServerDataTypeCollection.NVARCHAR:
                        case SqlServerDataTypeCollection.CHAR:
                        case SqlServerDataTypeCollection.VARCHAR:
                        case SqlServerDataTypeCollection.TEXT:
                        case SqlServerDataTypeCollection.NTEXT:
                        default:
                            {
                                column.DataType = new DataTypeSchema(DotNetDataType.String.ToString(), column.DataType.Length,
                                    -1, -1, column.DataType.IsAutoIncrement);
                                break;
                            }
                    }
                }
            }
        }

        public void Transformation(DatabaseSchema database)
        {
            database.Info = new DatabaseInfo();

            database.Info.DatabaseType = DatabaseType.SqlServer;

            foreach (TableSchema table in database.Tables)
            {
                foreach (ColumnSchema column in table.Columns)
                {
                    DotNetDataType type = Pixysoft.EnumCoder.ToEnum<DotNetDataType>(column.DataType.DataType);

                    switch (type)
                    {
                        case DotNetDataType.Boolean://2010-08-08
                        case DotNetDataType.Char://2010-08-08
                        case DotNetDataType.Int32:
                            column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.INT.ToString(),
                                -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;
                        case DotNetDataType.Int64:
                            column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.BIGINT.ToString(),
                                 -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.Decimal:
                        case DotNetDataType.Single:
                        case DotNetDataType.Double:
                            column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.NUMERIC.ToString(),
                                -1,
                                column.DataType.Precision > 38 ? 38 : column.DataType.Precision,
                                column.DataType.Scale > 12 ? 12 : column.DataType.Scale, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.DateTime:
                            column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.DATETIME.ToString(),
                                -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.Byte:
                            column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.IMAGE.ToString(),
                                -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.String:
                        default:
                            if (column.DataType.Length <= 0)
                            {
                                //09-09-27 

                                //Text is a BLOB or Binary Large Object data type. It stores data, generally, not in the table, but rather in a space out on the file system. As such, it behaves totally differently than the other string data types CHAR, VARCHAR, NVARCHAR, NCHAR. That includes not being able to run aggregates against it.

                                column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.VARCHAR.ToString(),
                                    default_mid_varcharlength, -1, -1, column.DataType.IsAutoIncrement);

                                //if (database.IsPrimaryKey(column) ||
                                //    database.IsUnique(column) ||
                                //    database.IsIndex(column))
                                //{
                                //    column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.VARCHAR.ToString(), 400, -1, -1);
                                //}
                                //else
                                //{
                                //    column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.TEXT.ToString(), -1, -1, -1);
                                //}

                            }
                            else
                            {
                                column.DataType = new DataTypeSchema(SqlServerDataTypeCollection.VARCHAR.ToString(),
                                     column.DataType.Length > 8000 ? 8000 : column.DataType.Length,
                                      -1,
                                      -1,
                                      column.DataType.IsAutoIncrement);
                            }
                            break;
                    }
                }
            }
        }

        public List<string> GetDataTypeList()
        {
            return Pixysoft.EnumCoder.ToList<SqlServerDataTypeCollection>();
        }

        public List<string> GetDataTypeRegexList()
        {
            List<string> list = new List<string>();
            list.Add(@"[\w]+\([\d]+\)[,]{0,1}");
            list.Add(@"[\w]+\([\d]+[,\d]*\)[,]{0,1}");

            foreach (string type in Pixysoft.EnumCoder.ToList<SqlServerDataTypeCollection>())
                list.Add(type + "[,]{0,1}");

            return list;
        }

        public List<string> GetKeyWordsList()
        {
            return systemNameList;
        }

        public bool IsValidName(string name)
        {
            if (name == null)
                return false;

            if (name.Trim() == "")
                return false;

            if (systemNameList.Contains(name.ToUpper()))
                return false;

            if (name.Length > 30)
                return false;

            if (Regex.Match(name, "[a-z,A-Z]{1}[a-z,A-Z,1-9,_,$,#]*").Value != name)
                return false;

            return true;
        }

        public string ValidateDefaultValue(string input, DataTypeSchema datatype)
        {
            if (input == null)
                return null;

            SqlServerDataTypeCollection datatypeEnum = Pixysoft.EnumCoder.ToEnum<SqlServerDataTypeCollection>(datatype.DataType);

            switch (datatypeEnum)
            {
                case SqlServerDataTypeCollection.VARBINARY:
                case SqlServerDataTypeCollection.BINARY:
                case SqlServerDataTypeCollection.SQL_VARIANT:
                case SqlServerDataTypeCollection.IMAGE:
                    {
                        return input;
                    }



                case SqlServerDataTypeCollection.SMALLINT:
                case SqlServerDataTypeCollection.TINYINT:
                case SqlServerDataTypeCollection.BIT:
                case SqlServerDataTypeCollection.INT:
                case SqlServerDataTypeCollection.BIGINT:
                    try
                    {
                        return Int64.Parse(input).ToString();
                    }
                    catch { return null; }




                case SqlServerDataTypeCollection.DATETIME:
                case SqlServerDataTypeCollection.SMALLDATETIME:
                case SqlServerDataTypeCollection.TIMESTAMP:
                    try
                    {
                        return DateTime.Parse(input).ToString();
                    }
                    catch { return null; }



                case SqlServerDataTypeCollection.DECIMAL:
                case SqlServerDataTypeCollection.NUMERIC:
                case SqlServerDataTypeCollection.REAL:
                case SqlServerDataTypeCollection.FLOAT:
                case SqlServerDataTypeCollection.MONEY:
                case SqlServerDataTypeCollection.SMALLMONEY:
                    try
                    {
                        return Double.Parse(input).ToString();
                    }
                    catch { return null; }


                case SqlServerDataTypeCollection.UNIQUEIDENTIFIER:
                case SqlServerDataTypeCollection.NCHAR:
                case SqlServerDataTypeCollection.NVARCHAR:
                case SqlServerDataTypeCollection.CHAR:
                case SqlServerDataTypeCollection.VARCHAR:
                case SqlServerDataTypeCollection.TEXT:
                case SqlServerDataTypeCollection.NTEXT:
                default:
                    try
                    {
                        return Double.Parse(input).ToString();
                    }
                    catch
                    {
                        return "'" + input.Replace("'", "") + "'";
                    }
            }
        }

        public ISqlBuilder SqlBuilder
        {
            get
            {
                return SqlServerSqlBuilder.Instance;
            }
        }

        public void CreateDatabase(ISchemaCommand command, string databasename)
        {
            command.SQL = SqlBuilder.CreateDatabase(databasename);

            command.Execute();
        }

        public void DropDatabase(ISchemaCommand command, string databasename)
        {
            command.SQL = SqlBuilder.DropDatabase(databasename);

            command.Execute();
        }
    }
}
